from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

# 获取数据库类型和配置
DB_TYPE = os.getenv("DB_TYPE", "sqlite").lower()

# 根据数据库类型构建连接字符串
if DB_TYPE == "sqlite":
    DB_PATH = os.getenv("DB_PATH", "./usermails.db")
    DATABASE_URL = f"sqlite:///{DB_PATH}"
    # SQLite配置：增加超时时间，避免多进程并发时的数据库锁定问题
    connect_args = {
        "check_same_thread": False,
        "timeout": 60,  # 增加到60秒超时（Gmail同步慢需要更长时间）
        "isolation_level": None  # 自动提交模式，减少锁定时间
    }
    
elif DB_TYPE == "mysql":
    DB_HOST = os.getenv("DB_HOST", "localhost")
    DB_PORT = os.getenv("DB_PORT", "3306")
    DB_USER = os.getenv("DB_USER", "root")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "")
    DB_NAME = os.getenv("DB_NAME", "usermails")
    DB_CHARSET = os.getenv("DB_CHARSET", "utf8mb4")
    
    DATABASE_URL = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}?charset={DB_CHARSET}"
    connect_args = {
        "connect_timeout": 15,
        "charset": DB_CHARSET
    }
    
elif DB_TYPE == "postgresql" or DB_TYPE == "pgsql":
    DB_HOST = os.getenv("DB_HOST", "localhost")
    DB_PORT = os.getenv("DB_PORT", "5432")
    DB_USER = os.getenv("DB_USER", "postgres")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "")
    DB_NAME = os.getenv("DB_NAME", "usermails")
    
    DATABASE_URL = f"postgresql+psycopg2://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    connect_args = {
        "connect_timeout": 15
    }
    
else:
    raise ValueError(f"不支持的数据库类型: {DB_TYPE}，支持的类型: sqlite, mysql, postgresql")

# 创建数据库引擎
engine = create_engine(
    DATABASE_URL,
    connect_args=connect_args,
    pool_pre_ping=True,  # 自动重连
    pool_recycle=3600,   # 连接池回收时间（秒）
    echo=os.getenv("DB_ECHO", "false").lower() == "true"  # 是否打印SQL语句
)

# 如果是SQLite，启用WAL模式以支持并发读写
if DB_TYPE == "sqlite":
    from sqlalchemy import event
    
    @event.listens_for(engine, "connect")
    def set_sqlite_pragma(dbapi_conn, connection_record):
        cursor = dbapi_conn.cursor()
        # 启用WAL模式（Write-Ahead Logging），允许读写并发
        cursor.execute("PRAGMA journal_mode=WAL")
        # 设置同步模式为NORMAL，平衡性能和安全性
        cursor.execute("PRAGMA synchronous=NORMAL")
        # 增加缓存大小（以页为单位，默认-2000约为2MB）
        cursor.execute("PRAGMA cache_size=-16000")  # 16MB缓存（提高并发性能）
        # 设置繁忙超时（毫秒）- Gmail同步需要更长时间
        cursor.execute("PRAGMA busy_timeout=60000")  # 60秒
        # 设置临时存储为内存模式，提高性能
        cursor.execute("PRAGMA temp_store=MEMORY")
        # 设置页面大小（4KB，适合现代文件系统）
        cursor.execute("PRAGMA page_size=4096")
        cursor.close()

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 基础模型类
Base = declarative_base()

def get_db():
    """
    获取数据库会话的依赖函数
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
